﻿/*
 *author: 飞讯无线
 *date:2011-11-08
 * 
 */
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;

namespace com.fly.sms
{
    //发送统计
    public class TongjiService {
        //发送统计
        public static DataTable tongji(string CustomerId,string start, string end)
        {
            return Db.find("SELECT ISNULL(sum(SplitTotal),0) vSplitTotal,ISNULL(sum(DestNumberTotal),0) vDestNumberTotal,ISNULL(sum(SubmitSplitTotal),0) vSubmitSplitTotal,ISNULL(sum(SubmitNumberTotal),0) vSubmitNumberTotal FROM SMSendHistory where CreateTime>'" + start + "' and CreateTime<'" + end + "' and CustomerId=" + CustomerId);
        }
    }
    
    //待发送短信明细
    public class SMSendDetailService
    {
        public static bool add(string MsgId, string DestNumber, int SplitCount)
        {
            return Db.update("insert into  SMSendDetail(MsgId,DestNumber,SplitCount) values('" + MsgId + "','" + DestNumber + "'," + SplitCount + ")");
        }

        public static DataTable find(string MsgId, int startRow, int pagePerSize)
        {
            return Db.find("SELECT * FROM  SMSendDetail where MsgId='" + MsgId + "'", startRow, pagePerSize);
        }

        public static DataTable find(string CustomerId,string start, string end, string DestNumber, int startRow, int pagePerSize)
        {
            return Db.find("SELECT a.Id,a.DestNumber,a.SplitCount,a.SubmitCount,a.LastSubmitTime,a.SubmitResponseId,a.SubmitResponseResultCode,a.SubmitResponseDesc,a.QueryResultCode,a.QuerySMSId,a.QueryReceiveTime,a.QueryState,b.* FROM  SMSendDetail a, SMSend b where a.MsgId=b.MsgId and b.CreateTime>='" + start + "' and b.CreateTime<'" + end + "' and a.DestNumber like '%" + DestNumber + "%' and b.CustomerId=" + CustomerId + " order by CreateTime desc", startRow, pagePerSize);
        }
    }

    //已处理短信明细
    public class SMSendDetailHistoryService
    {
        public static DataTable find(string MsgId, int startRow, int pagePerSize)
        {
            return Db.find("SELECT * FROM  SMSendDetailHistory where MsgId='" + MsgId + "'", startRow, pagePerSize);
        }
        /*
        public static DataTable find(string start, string end, string DestNumber, int startRow, int pagePerSize)
        {
            return Db.find("SELECT * FROM  SMSendDetailHistory where CreateTime>='" + start + "' and CreateTime<'" + end + "' and DestNumber ='%" + DestNumber + "%' order by CreateTime desc", startRow, pagePerSize);
        }*/

        public static DataTable find(string CustomerId, string start, string end, string DestNumber, int startRow, int pagePerSize)
        {
            return Db.find("SELECT a.Id,a.DestNumber,a.SplitCount,a.SubmitCount,a.LastSubmitTime,a.SubmitResponseId,a.SubmitResponseResultCode,a.SubmitResponseDesc,a.QueryResultCode,a.QuerySMSId,a.QueryReceiveTime,a.QueryState,b.* FROM  SMSendDetailHistory a, SMSendHistory b where a.MsgId=b.MsgId and b.CreateTime>='" + start + "' and b.CreateTime<'" + end + "' and a.DestNumber like '%" + DestNumber + "%'and b.CustomerId=" + CustomerId + " order by CreateTime desc", startRow, pagePerSize);
        }

    }

    //待发送短信
    public class SMSendService {
        //添加待发送短信
        public static bool add(string MsgId, string BatchId, string CustomerId, string SMTaskId, string Name, string MsgContent, int SplitTotal, int DestNumberTotal)
        {
            return Db.update("insert into  SMSend(MsgId,BatchId,CustomerId,SMTaskId,Name,MsgContent,SplitTotal,DestNumberTotal,VerifyStatus) values('" + MsgId + "','" + BatchId + "'," + CustomerId + ",'" + SMTaskId + "','" + Name + "','" + MsgContent + "'," + SplitTotal + "," + DestNumberTotal + ",1)");
        }

        public static string getId(string Name)
        {
            string MsgId = "-1";
            DataRow smsSend = Db.findSingle("SELECT MsgId FROM  SMSend where Name='" + Name + "' order by CreateTime desc");
            if (smsSend!=null)
            {
                MsgId = (string)smsSend["MsgId"];
            }

            return MsgId;
        }

        //查询待发送、分页查询
        public static DataTable find(string CustomerId, string start, string end, string Name, int startRow, int pagePerSize)
        {
            return Db.find("SELECT MsgId,BatchId,Name,SMTaskId,ClientMsgId,CustomerId,MsgContent,MaxSubmitNumber,SplitTotal,DestNumberTotal,CONVERT(varchar, CreateTime, 120 )+'' CreateTime,VerifyStatus,IP,IsActive FROM  SMSend where CreateTime>='" + start + "' and CreateTime<'" + end + "' and Name like '%" + Name + "%' and CustomerId=" + CustomerId + " order by CreateTime desc", startRow, pagePerSize);
        }
    }

    //已处理短信
    public class SMSendHistoryService
    {
        //查询、分页查询
        public static DataTable find(string CustomerId, string start, string end, string Name, int startRow, int pagePerSize)
        {
            return Db.find("SELECT MsgId,BatchId,Name,SMTaskId,ClientMsgId,CustomerId,MsgContent,MaxSubmitNumber,SplitTotal,DestNumberTotal,CONVERT(varchar, CreateTime, 120 )+'' CreateTime,VerifyStatus,IP,IsActive FROM  SMSendHistory where CreateTime>='" + start + "' and CreateTime<'" + end + "' and Name like '%" + Name + "%' and CustomerId=" + CustomerId + " order by CreateTime desc", startRow, pagePerSize);
        }
    }

    //发送计划任务
    public class SMTaskService {
        //添加计划任务
        public static bool add(string  Id,string BatchId, string CustomerId, string Name, string MsgContent, int SplitTotal, int DestNumberTotal, string TaskType, string FromDate, string ToDate, string Condition, string StartTime)
        {
            return Db.update("insert into SMTask(Id,BatchId,CustomerId,Name,MsgContent,SplitTotal,DestNumberTotal,TaskType,FromDate,ToDate,Condition,StartTime) values('" + Id + "','" + BatchId + "'," + CustomerId + ",'" + Name + "','" + MsgContent + "'," + SplitTotal + "," + DestNumberTotal + "," + TaskType + ",'" + FromDate + "','" + ToDate + "','" + Condition + "','" + StartTime + "')");
        }

        //查询计划任务、分页查询
        public static DataTable find(string CustomerId,string start, string end, string key, int startRow, int pagePerSize)
        {
            return Db.find("SELECT Id,BatchId,Name,ClientMsgId,CustomerId,MsgContent,MaxSubmitNumber,SplitTotal,DestNumberTotal,CONVERT(varchar, CreateTime, 120 ) CreateTime,VerifyStatus,TaskType,FromDate,ToDate,Condition,StartTime,LastRunDate,LastRunTime,IsActive,IP FROM SMTask where CreateTime>='" + start + "' and CreateTime<'" + end + "' and Name like '%" + key + "%' and CustomerId=" + CustomerId, startRow, pagePerSize);
        }

        //启用计划任务
        public static bool startTask(string SMTaskId)
        {
            return Db.update("update SMTask set IsActive=1 where Id='" + SMTaskId + "'");
        }

        //停止计划任务
        public static bool stopTask(string SMTaskId)
        {
            return Db.update("update SMTask set IsActive=0 where Id='" + SMTaskId + "'");
        }

        public static string getId(string Name)
        {
            string SMTaskId = "-1";
            DataRow smTask = Db.findSingle("SELECT Id FROM  SMTask where Name='" + Name + "' order by CreateTime desc");
            if (smTask != null)
            {
                SMTaskId = (string)smTask["Id"];
            }

            return SMTaskId;
        }

    }
    public class SMTaskHistoryService
    {
        //查询计划任务、分页查询
        public static DataTable find(string CustomerId,string start, string end, string Name, int startRow, int pagePerSize)
        {
            return Db.find("SELECT * FROM SMTaskHistory where CreateTime>='" + start + "' and CreateTime<'" + end + "' and Name like '%" + Name + "%' and CustomerId=" + CustomerId, startRow, pagePerSize);
        }
    }
    //发送计划任务明细
    public class SMTaskDetailService
    {
        //添加计划任务明细
        public static bool add(string SMTaskId, string DestNumber, int SplitCount)
        {
            return Db.update("insert into SMTaskDetail(SMTaskId,DestNumber,SplitCount) values('" + SMTaskId + "','" + DestNumber + "'," + SplitCount + ")");
        }

        //查询发送计划明细、分页查询
        public static DataTable find(string SMTaskId, int startRow, int pagePerSize)
        {
            return Db.find("SELECT * FROM SMTaskDetail where SMTaskId='" + SMTaskId + "'", startRow, pagePerSize);
        }
    }

    //发送计划任务历史明细
    public class SMTaskDetailHistoryService
    {
        //查询发送计划明细、分页查询
        public static DataTable find(string SMTaskId, int startRow, int pagePerSize)
        {
            return Db.find("SELECT * FROM SMTaskDetailHistory where SMTaskId='" + SMTaskId + "'", startRow, pagePerSize);
        }
    }

    //客户内容
    public class CustomerContentService {
        //查询客户内容、分页查询
        public static DataTable findByCustomer(string CustomerId, string Name, int startRow, int pagePerSize)
        {
            return Db.find("SELECT * FROM CustomerContent where CustomerId=" + CustomerId + " and Name like '%" + Name + "%'", startRow, pagePerSize);
        }

        //根据Id获取客户内容
        public static DataTable get(string Id)
        {
            return Db.find("SELECT * FROM CustomerContent where Id='" + Id + "'");
        }

        //更新客户内容
        public static bool update(string Id, string Name,string MsgContent)
        {
            return Db.update("update CustomerContent set Name='" + Name + "',MsgContent='" + MsgContent + "' where id='" + Id + "'");
        }

        //添加客户内容
        public static bool add(string CustomerId, string Name, string MsgContent)
        {
            return Db.update("insert into CustomerContent(Name,MsgContent,CustomerId) values('" + Name + "','" + MsgContent + "'," + CustomerId + ")");
        }

        //删除客户内容
        public static bool delete(string Id)
        {
            return Db.update("delete from CustomerContent where Id='" + Id + "'");
        }
    
    }

    //通讯录明细
    public class CustomerAddressBookDetailService
    {
        //查询号码池号码、需要分页查询
        public static DataTable findByAddressBookId(string AddressBookId, int startRow, int pagePerSize)
        {
            return Db.find("SELECT * FROM CustomerAddressBookDetail where AddressBookId='" + AddressBookId+"'", startRow, pagePerSize);
        }

        //根据Id获取通讯录号码
        public static DataTable get(string Id)
        {
            return Db.find("SELECT * FROM CustomerAddressBookDetail where Id='" + Id+"'");
        }


        //更新通讯录号码信息
        public static bool update(string Id, string Name,string Sex,string Remark,string DestNumber)
        {
            return Db.update("update CustomerAddressBookDetail set Name='" + Name + "',Sex=" + Sex + ",Remark='" + Remark + "', DestNumber='" + DestNumber + "' where id='" + Id+"'");
        }

        //添加通讯录号码
        public static bool add(string AddressBookId, string Name, string Sex, string Remark, string DestNumber)
        {
            return Db.update("insert into CustomerAddressBookDetail(AddressBookId,Name,Sex,Remark,DestNumber) values('" + AddressBookId + "','" + Name + "'," + Sex + ",'" + Remark + "','" + DestNumber + "')");
        }

        //删除通讯录号码
        public static bool delete(string Id)
        {
            return Db.update("delete from CustomerAddressBookDetail where Id='" + Id+"'");
        }
    }

    //通讯录
    public class CustomerAddressBookService
    {
        //查询通讯录、分页查询
        public static DataTable findByCustomer(string CustomerId, string Name, int startRow, int pagePerSize)
        {
            return Db.find("SELECT * FROM CustomerAddressBook where CustomerId=" + CustomerId + " and Name like '%" + Name + "%'", startRow, pagePerSize);
        }

        //根据Id获取通讯录
        public static DataTable get(string Id)
        {
            return Db.find("SELECT * FROM CustomerAddressBook where Id='" + Id+"'");
        }

        //更新通讯录名称
        public static bool update(string Id, string Name)
        {
            return Db.update("update CustomerAddressBook set Name='" + Name + "' where id='" + Id+"'");
        }

        //添加通讯录
        public static bool add(string CustomerId, string Name)
        {
            return Db.update("insert into CustomerAddressBook(Name,CustomerId) values('" + Name + "'," + CustomerId + ")");
        }

        //删除通讯录
        public static bool delete(string Id)
        {
            return Db.update("delete from CustomerAddressBook where Id='" + Id+"'");
        }
    }


    public class CustomerNumberPoolDetailService
    {
        //查询号码池号码、需要分页查询
        public static DataTable findByNumberPoolId(string NumberPoolId,int startRow,int pagePerSize)
        {
            return Db.find("SELECT * FROM CustomerNumberPoolDetail where NumberPoolId='" + NumberPoolId+"'", startRow, pagePerSize);
        }

        public static string[] findByNumberPoolId(string NumberPoolId)
        {
            DataTable dt = Db.find("SELECT DestNumber FROM CustomerNumberPoolDetail where NumberPoolId='" + NumberPoolId + "'");
            string[] numberarray = new string[dt.Rows.Count];
            int i = 0;
            foreach (DataRow row in dt.Rows)
            {
                numberarray[i] = (string)row["DestNumber"];
                i++;
            }
            return numberarray;
        }

    }

    public class CustomerNumberPoolService
    {
        //查询号码池、分页查询
        public static DataTable findByCustomer(string CustomerId, string key, int startRow, int pagePerSize)
        {
            return Db.find("SELECT * FROM CustomerNumberPool where CustomerId=" + CustomerId + " and Name like '%" + key + "%'",startRow,pagePerSize);
        }

        //根据Id获取号码池
        public static DataTable get(string Id)
        {
            return Db.find("SELECT * FROM CustomerNumberPool where Id='" + Id+"'");
        }

        //更新号码池名称
        public static bool update(string Id,string Name)
        {
            return Db.update("update CustomerNumberPool set Name='" + Name + "' where id='"+Id+"'");
        }

        //添加号码池
        public static bool add(string CustomerId, string Name)
        {
            return Db.update("insert into CustomerNumberPool(Name,CustomerId) values('" + Name + "''" + CustomerId + ")");
        }

        //删除号码池
        public static bool delete(string Id)
        {
            return Db.update("delete from CustomerNumberPool where Id='"+Id+"'");
        }
    }

    public class NotifyService
    {
        //查询某个客户的通告信息、分页查询
        public static DataTable findByCustomer(string CustomerId,int startRow,int pagePerSize)
        {
            return Db.find("SELECT * FROM Notify where CustomerId=" + CustomerId, startRow, pagePerSize);
        }
    }

    public class PublicNoticeService
    {
        //查询公告信息、分页查询
        public static DataTable list(int startRow, int pagePerSize)
        {
            return Db.find("SELECT Id,CONVERT(varchar, UpdateTime, 120 )+'' UpdateTime,Type,Topic,Body,UpdateEmployeeId,ExpireDate,IsActive,IsTop  FROM PublicNotice", startRow, pagePerSize);
        }
    }

    public class CustomerBalanceService
    {
        //查询客户结算、分页查询
        public static DataTable findByCustomer(string CustomerId,int startRow,int pagePerSize)
        {
            return Db.find("SELECT * FROM CustomerBalance where CustomerId=" + CustomerId,startRow,pagePerSize);
        }
    }

    public class CustomerPayWithCashService {
        //查询客户现金支付明细、分页查询
        public static DataTable findByCustomer(string CustomerId,int startRow,int pagePerSize)
        {
            return Db.find("SELECT Id,CustomerId,EmployeeId,PayAmount,ReceiveAmount,CreditsAmount,Remark,CONVERT(varchar, CreateTime, 120 ) CreateTime FROM CustomerPayWithCash where CustomerId=" + CustomerId + " order by CreateTime desc", startRow, pagePerSize);
        }
    }

    public class CustomerPayWithMsgCardService
    {
        //查询客户充值卡支付明细、分页查询
        public static DataTable findByCustomer(string CustomerId,int startRow,int pagePerSize)
        {
            return Db.find("SELECT * FROM CustomerPayWithMsgCard where CustomerId=" + CustomerId,startRow,pagePerSize);
        }
    }

    public class CustomerService
    {
        //根据Id获取客户信息
        public static DataTable get(int Id)
        {
            return Db.find("SELECT * FROM Customer where Id=" + Id);
        }
        //根据登录用户名查询客户信息
        public static DataTable findByLoginName(string LoginName)
        {
            return Db.find("SELECT * FROM Customer where LoginName='" + LoginName + "'");
        }

        //根据客户名查询客户信息
        public static DataTable findByName(string Name)
        {
            return Db.find("SELECT * FROM Customer where Name='" + Name + "'");
        }

        //根据登录名、登录密码查询客户信息
        private static DataRow find(string LoginName, string LoginPass)
        {
            return Db.findSingle("SELECT * FROM Customer where LoginName='" + LoginName + "' and LoginPass='" + LoginPass + "'");
        }
        //客户登录验证判断
        public static bool login(string LoginName, string LoginPass)
        {
            DataRow row = find(LoginName, LoginPass);
            if (row==null)
            {
                return false;
            }
            else
            {
                bool IsActive = (bool)row["IsActive"];
                return IsActive;
            }
        }
        //修改客户登录密码
        public static bool changepass(string LoginName, string LoginPass, string newPass)
        {
            DataRow row = find(LoginName, LoginPass);
            if (row == null)
            {
                return false;
            }
            else {
                string sql = "update Customer set LoginPass='" + newPass + "' where LoginName='" + LoginName + "'";
                return Db.update(sql);
            }
        }

    }

}